﻿using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Linq;
using ClientBankWPF.Entities;

namespace ClientBankWPF.Service
{
    public class DataAccessService
    {
        private readonly BankDBDAO bankDbDAO;

        #region Constructor

        public DataAccessService()
        {
            bankDbDAO = new BankDBDAO();
        }

        #endregion

        #region LocalUser Access

        public IList<LocalUser> GetLocalUsers()
        {
            ObjectQuery<LocalUser> localUsersQuery = bankDbDAO.LocalUsers;
            return localUsersQuery.OrderBy(x => x.UserName).ToList();
        }

        public LocalUser GetLocalUser(string name)
        {
            ObjectQuery<LocalUser> localUsersQuery = bankDbDAO.LocalUsers.Where("it.UserName = @UserName",
                    new ObjectParameter("UserName", name)).Include("Role");
            if (localUsersQuery.Count() == 1)
            {
                return localUsersQuery.First();
            }
            return null;
        }

        public string AddLocalUser(LocalUser newLocalUser)
        {
            try
            {
                bankDbDAO.AddToLocalUsers(newLocalUser);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string UpdateLocalUserWithPassword(LocalUser newLocalUser)
        {
            try
            {
                LocalUser localUser = (from lu in bankDbDAO.LocalUsers
                                       where lu.Id == newLocalUser.Id
                                       select lu).First();
                localUser.Password = newLocalUser.Password;
                localUser.IdRole = newLocalUser.IdRole;
                localUser.UserName = newLocalUser.UserName;
                localUser.IsActive = newLocalUser.IsActive;
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string UpdateLocalUser(LocalUser newLocalUser)
        {
            try
            {
                LocalUser localUser = (from lu in bankDbDAO.LocalUsers
                                       where lu.Id == newLocalUser.Id
                                       select lu).First();
                localUser.IdRole = newLocalUser.IdRole;
                localUser.UserName = newLocalUser.UserName;
                localUser.IsActive = newLocalUser.IsActive;
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string DeleteLocalUser(LocalUser localUser)
        {
            try
            {
                LocalUser localUserForDeleting = (from lu in bankDbDAO.LocalUsers
                                                  where lu.Id == localUser.Id
                                                  select lu).First();
                bankDbDAO.DeleteObject(localUserForDeleting);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        #endregion

        #region Client Access

        public IList<Client> GetClients()
        {
            ObjectQuery<Client> clientsQuery = bankDbDAO.Clients.Include("Deposits");
            return clientsQuery.OrderBy(x => x.Name).ToList();
        }

        public Client GetClient(string name)
        {
            ObjectQuery<Client> clientsQuery = bankDbDAO.Clients.Where("it.Name = @Name",
                    new ObjectParameter("Name", name));
            return clientsQuery.Count() == 1 ? clientsQuery.First() : null;
        }

        public Client GetClientByUNP(decimal unp)
        {
            ObjectQuery<Client> clientsQuery = bankDbDAO.Clients.Where("it.UNP = @Unp",
                    new ObjectParameter("Unp", unp));
            if (clientsQuery.Count() == 1)
            {
                return clientsQuery.First();
            }
            return null;
        }

        public decimal? GetMaxUNP()
        {
            return bankDbDAO.Clients.Max(x => (decimal?)x.UNP);
        }

        public string AddClient(Client newClient)
        {
            try
            {
                bankDbDAO.AddToClients(newClient);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string UpdateClient(Client newClient)
        {
            try
            {
                Client client = (from c in bankDbDAO.Clients
                                 where c.Id == newClient.Id
                                 select c).First();
                client.Name = newClient.Name;
                client.Address = newClient.Address;
                client.CFO = newClient.CFO;
                client.Description = newClient.Description;
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string DeleteClient(Client client, out bool isDeleted)
        {
            try
            {
                isDeleted = false;
                Client clientForDeleting = (from c in bankDbDAO.Clients
                                            where c.Id == client.Id
                                            select c).First();
                List<Deposit> deposits = clientForDeleting.Deposits.ToList();
                string resultPositiveSum = "", resultNegativeSum = "", resultDeleted = "";
                //var positiveSumNumbers = new List<decimal>();
                //var negativeSumNumbers = new List<decimal>();
                //var deletedNumbers = new List<decimal>();
                foreach (var deposit in deposits)
                {
                    if (deposit.Sum < 0)
                    {
                        resultNegativeSum += deposit.Number + ", ";
                        //negativeSumNumbers.Add(deposit.Number);
                    }
                    if (deposit.Sum > 0)
                    {
                        resultPositiveSum += deposit.Number + ", ";
                        //positiveSumNumbers.Add(deposit.Number);
                    }
                    if (deposit.Sum == 0)
                    {
                        resultDeleted += deposit.Number + ", ";
                        string resultDelete = DeleteDeposit(deposit.Id);
                        if (!String.IsNullOrEmpty(resultDelete))
                        {
                            return resultDelete;
                        }
                        //deletedNumbers.Add(deposit.Number);
                    }
                }
                string result = "";
                if (!String.IsNullOrEmpty(resultNegativeSum))
                {
                    resultNegativeSum = resultNegativeSum.Trim(' ', ',');
                    result = "На счетах " + resultNegativeSum + " задолженность. Для удаления клиента надо её погасить.\n";
                }
                if (!String.IsNullOrEmpty(resultPositiveSum))
                {
                    resultPositiveSum = resultPositiveSum.Trim(' ', ',');
                    result += "На счетах " + resultPositiveSum + " остались средства.";
                }
                if (String.IsNullOrEmpty(resultNegativeSum) && String.IsNullOrEmpty(resultPositiveSum))
                {
                    resultDeleted = resultDeleted.Trim(' ', ',');
                    result += "Счета " + resultDeleted + " были закрыты.";
                    bankDbDAO.DeleteObject(clientForDeleting);
                    bankDbDAO.SaveChanges();
                    isDeleted = true;
                }
                return result;
            }
            catch (Exception exception)
            {
                isDeleted = false;
                return exception.Message;
            }
        }

        #endregion

        #region Deposit Access

        public string AddDeposit(Deposit newDeposit)
        {
            try
            {
                bankDbDAO.AddToDeposits(newDeposit);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public IList<Deposit> GetDeposits(Guid idClient)
        {
            return bankDbDAO.Deposits.Where(x => x.IdClient == idClient).OrderBy(x => x.Number).ToList();
        }

        public Deposit GetDeposit(decimal depositNumber)
        {
            ObjectQuery<Deposit> depositsQuery = bankDbDAO.Deposits.Where("it.Number = @Number",
                    new ObjectParameter("Number", depositNumber));
            return depositsQuery.Count() == 1 ? depositsQuery.First() : null;
        }

        public decimal? GetMaxDepositNumber()
        {
            return bankDbDAO.Deposits.Max(x => (decimal?)x.Number);
        }

        public string UpdateDeposit(Deposit newDeposit)
        {
            try
            {
                Deposit deposit = (from d in bankDbDAO.Deposits
                                   where d.Id == newDeposit.Id
                                   select d).First();
                deposit.Sum = newDeposit.Sum;
                deposit.CurrencyType = newDeposit.CurrencyType;
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string DeleteDeposit(Deposit deposit)
        {
            return DeleteDeposit(deposit.Id);
        }

        public string DeleteDeposit(Guid idDeposit)
        {
            try
            {
                IList<DepositLog> depositLogsQuery = bankDbDAO.DepositLogs.Where(x => x.IdDeposit == idDeposit).ToList();
                foreach (var depositLog in depositLogsQuery)
                {
                    bankDbDAO.DeleteObject(depositLog);
                }
                Deposit depositForDeleting = (from d in bankDbDAO.Deposits
                                              where d.Id == idDeposit
                                              select d).First();
                bankDbDAO.DeleteObject(depositForDeleting);
                bankDbDAO.SaveChanges();
                return String.Empty;

            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        #endregion

        #region Role Access
        public IList<Role> GetRoles()
        {
            ObjectQuery<Role> rolesQuery = bankDbDAO.Roles;
            return rolesQuery.OrderBy(x => x.RoleName).ToList();
        }
        #endregion

        #region Bank Access

        public decimal? GetMaxBankCode()
        {
            return bankDbDAO.Banks.Max(x => (decimal?)x.Code);
        }

        public IList<Bank> GetBanks()
        {
            ObjectQuery<Bank> banksQuery = bankDbDAO.Banks;
            return banksQuery.OrderBy(x => x.Name).ToList();
        }

        public Bank GetBank(string name)
        {
            ObjectQuery<Bank> banksQuery = bankDbDAO.Banks.Where("it.Name = @Name",
                    new ObjectParameter("Name", name));
            if (banksQuery.Count() == 1)
            {
                return banksQuery.First();
            }
            return null;
        }

        public string AddBank(Bank newBank)
        {
            try
            {
                bankDbDAO.AddToBanks(newBank);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string DeleteBank(Guid idBank)
        {
            try
            {
                Bank bankForDeleting = (from b in bankDbDAO.Banks
                                        where b.Id == idBank
                                        select b).First();
                bankDbDAO.DeleteObject(bankForDeleting);
                bankDbDAO.SaveChanges();
                return String.Empty;

            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public string UpdateBank(Bank newBank)
        {
            try
            {
                Bank bank = (from b in bankDbDAO.Banks
                             where b.Id == newBank.Id
                             select b).First();
                bank.Name = newBank.Name;
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        #endregion

        #region PaymentOrder Access

        public IEnumerable<PaymentOrder> GetPaymentOrdersByClientUnp(string clientUnp)
        {
            IEnumerable<PaymentOrder> paymentOrders = bankDbDAO.PaymentOrders.Where("it.PayerUNP = @PayerUNP",
                      new ObjectParameter("PayerUNP", decimal.Parse(clientUnp)));

            return paymentOrders;
        }

        public IList<PaymentOrder> GetPaymentOrders(Guid currentUserId)
        {
            List<PaymentOrder> paymentOrders = bankDbDAO.PaymentOrders.Include("State").Include("LocalUser").
                Where("it.State.TransactionState = @State", new ObjectParameter("State", "принято")).Top("5").
                OrderBy(x => x.Date).ToList();
            State state = GetState("обрабатывается");
            foreach (var paymentOrder in paymentOrders)
            {
                PaymentOrder order1 = paymentOrder;
                PaymentOrder tmpOrder = (from order in bankDbDAO.PaymentOrders
                                         where order.Id == order1.Id
                                         select order).First();
                tmpOrder.IdState = state.Id;
                tmpOrder.IdOperationOfficer = currentUserId;
                bankDbDAO.SaveChanges();
            }
            return paymentOrders.OrderBy(x => x.Date).ToList();
        }


        public IList<PaymentOrder> GetPaymentOrders(DateTime dateTimeFrom, DateTime dateTimeTo)
        {
            return bankDbDAO.PaymentOrders.Include("State").Include("LocalUser").Where(x => x.Date > dateTimeFrom)
                .Where(x => x.Date < dateTimeTo).OrderBy(x => x.Date).ToList();
        }

        public void ResetPaymentOrder(Guid paymentOrderId)
        {
            PaymentOrder paymentOrder = (from po in bankDbDAO.PaymentOrders
                                         where po.Id == paymentOrderId
                                         select po).First();
            State state = GetState("принято");
            paymentOrder.IdState = state.Id;
            paymentOrder.IdOperationOfficer = null;
            bankDbDAO.SaveChanges();
        }

        public PaymentOrder GetPaymentOrder(Guid paymentOrderId)
        {
            IQueryable<PaymentOrder> poLst = (from order in bankDbDAO.PaymentOrders
                                              where order.Id == paymentOrderId
                                              select order);
            if (poLst.Count() > 0)
            {
                return poLst.First();
            }
            return null;
        }

        public void ProcessingPaymentOrder(Guid paymentOrderId, bool isAccepted, string comment)
        {
            PaymentOrder tmpOrder = (from order in bankDbDAO.PaymentOrders
                                     where order.Id == paymentOrderId
                                     select order).First();
            State state = GetState(isAccepted ? "проведено" : "отклонено");
            tmpOrder.IdState = state.Id;
            if (!String.IsNullOrEmpty(comment))
            {
                tmpOrder.Comment = comment;
            }
            bankDbDAO.SaveChanges();
        }

        public string AddPaymentOrder(PaymentOrder paymentOrder)
        {
            //            try NOTE: TOL: что за покрывательство ошибок?..
            //            {
            bankDbDAO.AddToPaymentOrders(paymentOrder);
            bankDbDAO.SaveChanges();
            return String.Empty;
            //            }
            //            catch (Exception exception)
            //            {
            //                return exception.Message;
            //            }
        }

        public string DeletePaymentOrder(PaymentOrder paymentOrder)
        {
            return DeletePaymentOrder(paymentOrder.Id);
        }

        public string DeletePaymentOrder(Guid paymentOrderId)
        {
            try
            {
                PaymentOrder paymentOrderForDeleting = (from po in bankDbDAO.PaymentOrders
                                                        where po.Id == paymentOrderId
                                                        select po).First();
                bankDbDAO.DeleteObject(paymentOrderForDeleting);
                bankDbDAO.SaveChanges();
                return String.Empty;

            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        #endregion

        #region State Access

        public State GetState(string stateName)
        {
            return bankDbDAO.States.Where("it.TransactionState = @StateName", new ObjectParameter("StateName", stateName)).First();
        }

        #endregion

        #region Transaction Access

        public string AddTransactionRecord(Transaction transaction)
        {
            try
            {
                bankDbDAO.AddToTransactions(transaction);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public IList<Transaction> GetTransactions()
        {
            ObjectQuery<Transaction> transactionsQuery = bankDbDAO.Transactions;
            return transactionsQuery.OrderBy(x => x.Date).ToList();
        }

        #endregion

        #region DepositLog Access

        public string AddDepositLogRecord(DepositLog depositLog)
        {
            try
            {
                bankDbDAO.AddToDepositLogs(depositLog);
                bankDbDAO.SaveChanges();
                return String.Empty;
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        public IList<DepositLog> GetDepositLogs(Guid depositId)
        {
            return bankDbDAO.DepositLogs.Where(x => x.IdDeposit == depositId).OrderBy(x => x.Date).ToList();
        }

        #endregion

        #region Partner Access

        public IList<Partner> GetPartners(Guid idClient)
        {
            return bankDbDAO.Partners.Include("PartnerDeposits").Where(x => x.IdClient == idClient).
                OrderBy(x => x.Name).ToList();
        }

        public void AddPartner(Partner newPartner)
        {
            bankDbDAO.AddToPartners(newPartner);
            bankDbDAO.SaveChanges();
        }

        public void UpdatePartner(Partner newPartner)
        {
            Partner partner = (from p in bankDbDAO.Partners
                               where p.Id == newPartner.Id
                               select p).First();
            partner.UNP = newPartner.UNP;
            bankDbDAO.SaveChanges();
        }

        public void AddPartnersDeposit(PartnerDeposit newPartnersDeposit)
        {
            bankDbDAO.AddToPartnerDeposits(newPartnersDeposit);
            bankDbDAO.SaveChanges();
        }

        public void UpdatePartnersDeposit(PartnerDeposit newPartnersDeposit)
        {
            PartnerDeposit partnerDeposit = (from pd in bankDbDAO.PartnerDeposits
                                             where pd.Id == newPartnersDeposit.Id
                                             select pd).First();
            partnerDeposit.IdBank = newPartnersDeposit.IdBank;
            bankDbDAO.SaveChanges();
        }

        public int? GetMaxPaymentOrderNumber(decimal unp)
        {
            return bankDbDAO.PaymentOrders.Where(x => x.PayerUNP == unp).Max(x => (int?)x.Number);
        }

        #endregion
    }
}